package com.maodou.android.utils

import jxl.Workbook
import org.dhatim.fastexcel.reader.CellType
import org.dhatim.fastexcel.reader.ReadableWorkbook
import java.io.InputStream
// 新增导出相关依赖
import android.content.ContentValues
import android.content.Context
import android.net.Uri
import android.os.Build
import android.os.Environment
import android.provider.MediaStore
import android.provider.OpenableColumns
import com.maodou.core.utils.Logger
import java.io.File
import java.io.FileOutputStream
import java.nio.charset.Charset
import java.time.LocalDate
import java.time.format.DateTimeFormatter

/**
 * @Author Xing
 * @CreateTime 2025年09月23日
 * @Description
 */
object ExcelUtils {

    // --- 解析实现 ---
    fun parseCsv(input: InputStream): Pair<List<String>, List<List<String>>> {
        val lines = input.bufferedReader(Charsets.UTF_8).readLines()
            .dropWhile { it.isBlank() }
        if (lines.isEmpty()) return emptyList<String>() to emptyList()
        val sep = detectSeparator(lines.first())
        val header = parseCsvLine(lines.first(), sep)
        val rows = lines.drop(1)
            .filter { it.isNotEmpty() }
            .map { parseCsvLine(it, sep) }
        return header to rows
    }

    // 新增：解析以竖线管道符 '|' 分隔的 TXT 文件（首行为表头）
    fun parsePipeTxt(input: InputStream): Pair<List<String>, List<List<String>>> {
        val lines = input.bufferedReader(Charsets.UTF_8).readLines()
            .dropWhile { it.isBlank() }
        if (lines.isEmpty()) return emptyList<String>() to emptyList()
        val header = parseCsvLine(lines.first(), '|')
        val rows = lines.drop(1)
            .filter { it.isNotEmpty() }
            .map { parseCsvLine(it, '|') }
        return header to rows
    }

    fun detectSeparator(header: String): Char {
        val candidates = charArrayOf(',', '，', ';', '\t')
        return candidates.maxByOrNull { c -> header.count { it == c } } ?: ','
    }

    fun parseCsvLine(line: String, sep: Char): List<String> {
        val result = mutableListOf<String>()
        val sb = StringBuilder()
        var inQuotes = false
        var i = 0
        while (i < line.length) {
            val ch = line[i]
            when (ch) {
                '"' -> {
                    if (inQuotes && i + 1 < line.length && line[i + 1] == '"') {
                        // 转义双引号
                        sb.append('"')
                        i++
                    } else {
                        inQuotes = !inQuotes
                    }
                }
                sep -> if (!inQuotes) {
                    result.add(sb.toString())
                    sb.clear()
                } else sb.append(ch)
                else -> sb.append(ch)
            }
            i++
        }
        result.add(sb.toString())
        return result
    }

    fun parseXlsx(input: InputStream): Pair<List<String>, List<List<String>>> {
        ReadableWorkbook(input).use { wb ->
            val sheet = wb.firstSheet
            val rowsData = mutableListOf<List<String>>()
            var header: List<String>? = null
            sheet.openStream().use { stream ->
                stream.forEach { row ->
                    val cols = (0 until row.cellCount).map { idx ->
                        val cell = row.getCell(idx)
                        when (cell?.type) {
                            CellType.BOOLEAN -> cell.asBoolean().toString()
                            CellType.NUMBER -> cell.text
                            CellType.FORMULA -> cell.text
                            CellType.STRING -> cell.text
                            CellType.ERROR, CellType.EMPTY -> ""
                            else -> ""
                        }
                    }
                    if (header == null) header = cols else rowsData.add(cols)
                }
            }
            return (header ?: emptyList()) to rowsData
        }
    }

    fun parseXls(input: InputStream): Pair<List<String>, List<List<String>>> {
        val wb = Workbook.getWorkbook(input)
        try {
            val sheet = wb.getSheet(0)
            if (sheet.rows == 0) return emptyList<String>() to emptyList()
            val header = sheet.getRow(0).map { it.contents }
            val colCount = maxOf(header.size, sheet.columns)
            val rows = mutableListOf<List<String>>()
            for (r in 1 until sheet.rows) {
                val rowCells = sheet.getRow(r)
                val row = (0 until colCount).map { c ->
                    if (c < rowCells.size) rowCells[c].contents else ""
                }
                rows.add(row)
            }
            return header to rows
        } finally {
            try { wb.close() } catch (_: Exception) {}
        }
    }

    /**
     * 将导入的表头映射到 AccountEntry 的字段索引
     */
    fun buildColumnIndex(header: List<String>): Map<String, Int> {
        val normalized = header.map { normalizeHeaderLabel(it) }
        val synonyms = mapOf(
            "accountID" to listOf("账号id","账号","帐号","账户","accountid","account","id","uid","用户名","user","login"),
            "nickname" to listOf("昵称","备注","显示名","姓名","name","nickname"),
            "password" to listOf("密码","pass","pwd","password"),
            "phone" to listOf("手机号","手机","电话","phone","mobile"),
            "twoFa" to listOf("2fa","twofa","totp","otp","秘钥","密钥","secret"),
            "email" to listOf("邮箱","email","mail","电子邮箱"),
            "emailPassword" to listOf("邮箱密码","emailpwd","mailpwd","emailpassword"),
            "ipCountry" to listOf("ip","国家地区","国家/地区","国家","地区","country","region","ipcountry"),
            "cookie" to listOf("原始cookie","cookie","cookies"),
            "authCode" to listOf("md5","认证令牌","signature","sign"),
            "type" to listOf("类型","分类","分组","type","category","group"),
            "tags" to listOf("标签","tags","tag")
        ).mapValues { (_, v) -> v.map { normalizeHeaderLabel(it) } }
        val result = mutableMapOf<String, Int>()
        synonyms.forEach { (field, keys) ->
            var found = -1
            for (i in normalized.indices) {
                if (keys.contains(normalized[i]) || normalized[i] == field) {
                    found = i; break
                }
            }
            if (found < 0) {
                for (i in normalized.indices) {
                    val h = normalized[i]
                    if (keys.any { k -> h.contains(k) }) { found = i; break }
                }
            }
            if (found >= 0) result[field] = found
        }
        return result
    }

    /**
     * 归一化表头：去空格与常见符号、转小写
     */
    fun normalizeHeaderLabel(label: String): String {
        return label.lowercase()
            .replace(" ", "")
            .replace("-", "")
            .replace("_", "")
            .replace("/", "")
            .replace("|", "")
            .replace("：", "")
            .replace(":", "")
            .replace("（", "")
            .replace("）", "")
            .replace("(", "")
            .replace(")", "")
            .trim()
    }

    /**
     * 导出账号为 CSV（UTF-8 BOM），优先保存到 公共下载目录（Android 10+），返回保存的 Uri；失败返回 null。
     */
    fun exportAccountsToCsv(
        context: Context,
        accounts: List<com.maodou.core.datastore.db.table.AccountEntry>,
        fileName: String = defaultCsvFileName()
    ): Uri? {
        if (accounts.isEmpty()) return null
        val csvBytes = buildAccountsCsv(accounts)

        return if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.Q) {
            val values = ContentValues().apply {
                put(MediaStore.MediaColumns.DISPLAY_NAME, fileName)
                put(MediaStore.MediaColumns.MIME_TYPE, "text/csv")
                put(MediaStore.Downloads.RELATIVE_PATH, Environment.DIRECTORY_DOWNLOADS)
            }
            val uri = context.contentResolver.insert(MediaStore.Downloads.EXTERNAL_CONTENT_URI, values)
            if (uri != null) {
                context.contentResolver.openOutputStream(uri)?.use { os ->
                    os.write(csvBytes)
                    os.flush()
                }
            }
            uri
        } else {
            // 无需外部存储权限，写入应用专属外部目录（Downloads 子目录）
            val dir = context.getExternalFilesDir(Environment.DIRECTORY_DOWNLOADS) ?: return null
            if (!dir.exists()) dir.mkdirs()
            val file = File(dir, fileName)
            runCatching {
                FileOutputStream(file).use { fos ->
                    fos.write(csvBytes)
                    fos.flush()
                }
            }.onFailure { return null }
            Uri.fromFile(file)
        }
    }

    private fun defaultCsvFileName(): String = "accounts_" + System.currentTimeMillis() + ".csv"

    private fun buildAccountsCsv(accounts: List<com.maodou.core.datastore.db.table.AccountEntry>): ByteArray {
        val header = listOf(
            "账号ID", "昵称", "登录密码", "手机号", "2FA", "邮箱", "邮箱密码", "国家/地区", "Cookie", "认证令牌", "类型", "平台", "标签", "状态", "最后检查时间", "业务缓存key", "用户ID"
        )
        val lines = StringBuilder()
        val bom = "\uFEFF"
        lines.append(bom)
        lines.append(toCsvLine(header)).append('\n')
        accounts.forEach { a ->
            val row = listOf(
                a.accountID,
                a.nickname ?: "",
                a.password ?: "",
                a.phone ?: "",
                a.twoFa ?: "",
                a.email ?: "",
                a.emailPassword ?: "",
                a.ipCountry ?: "",
                a.cookie ?: "",
                a.authCode ?: "",
                a.type,
                a.platform,
                a.tags ?: "",
                a.online.toString(),
                (a.lastCheck ?: 0L).toString(),
                a.key,
                a.userId
            )
            lines.append(toCsvLine(row)).append('\n')
        }
        return lines.toString().toByteArray(Charset.forName("UTF-8"))
    }

    private fun toCsvLine(cells: List<String>): String {
        return cells.joinToString(",") { escapeCsv(it) }
    }

    private fun escapeCsv(field: String): String {
        var needsQuote = false
        if (field.contains('"') || field.contains(',') || field.contains('\n') || field.contains('\r')) {
            needsQuote = true
        }
        val escaped = field.replace("\"", "\"\"")
        return if (needsQuote) "\"$escaped\"" else escaped
    }

    fun getFileName(appContext:Context,uri: Uri): String? {
        return try {
            appContext.contentResolver.query(uri, arrayOf(OpenableColumns.DISPLAY_NAME), null, null, null)?.use { cursor ->
                val index = cursor.getColumnIndex(OpenableColumns.DISPLAY_NAME)
                if (index >= 0 && cursor.moveToFirst()) cursor.getString(index) else null
            }
        } catch (e: Exception) {
            null
        }
    }

}